package com.thx.information.dao;

import com.thx.common.orm.PaginationDao;
import com.thx.information.HqlUtils;
import com.thx.information.model.Information;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class InfomationDao extends PaginationDao<Information> {

  private static final Logger logger = LoggerFactory.getLogger(InfomationDao.class);

  /**
   * 分页查询信息列表.
   * 
   * @param enterpriseId
   * @param personnelId
   * @param platformInfoId
   * @param issueStatus
   * @param infoTitle
   * @param readStatus
   * @return
   */
  public List<Information> findEnterpriseAndCommon(Long enterpriseId, Long personnelId,
      Long columnId, Long infoId, String infoType, String infoTitle, String issueStatus,
      String readStatus) {
    List<Information> list = new ArrayList<Information>();
    StringBuffer hql = new StringBuffer();
    hql.append("from Information");
    List<Object> objs = new ArrayList();
    this.generateAllAndCommonHql(hql, objs, enterpriseId, personnelId, columnId, infoId, infoType,
        infoTitle, issueStatus, readStatus);
    return this.findPageList(hql.toString(), objs.toArray());
  }

  /**
   * 查询所有信息列表.
   * 
   * @param enterpriseId
   * @param personnelId
   * @param platformInfoId
   * @param issueStatus
   * @param infoTitle
   * @param readStatus
   * @return
   */
  public List<Information> findAllEnterpriseAndCommon(Long enterpriseId, Long personnelId,
      Long columnId, Long infoId, String infoType, String infoTitle, String issueStatus,
      String readStatus) {
    StringBuffer hql = new StringBuffer();
    hql.append("from Information");
    List<Object> objs = new ArrayList();
    this.generateAllAndCommonHql(hql, objs, enterpriseId, personnelId, columnId, infoId, infoType,
        infoTitle, issueStatus, readStatus);
    return this.createQuery(hql.toString(), objs.toArray()).list();
  }

  /**
   * 查询所有信息列表数量 .
   * 
   * @param enterpriseId
   * @param personnelId
   * @param platformInfoId
   * @param issueStatus
   * @param infoTitle
   * @param readStatus
   * @return
   */
  public int findAllCountEnterpriseAndCommon(Long enterpriseId, Long personnelId, Long columnId,
      Long infoId, String infoType, String infoTitle, String issueStatus, String readStatus) {
    StringBuffer hql = new StringBuffer();
    hql.append("select count(*) from Information");
    List<Object> objs = new ArrayList();
    this.generateAllAndCommonHql(hql, objs, enterpriseId, personnelId, columnId, infoId, infoType,
        infoTitle, issueStatus, readStatus);
    Object obj = this.createQuery(hql.toString(), objs.toArray()).uniqueResult();
    return obj != null ? Integer.parseInt(obj.toString()) : 0;
  }
//
//  public List<Information> queryAll(boolean isCommon, Long enterpriseId, Long columnId,
//      String infoTitle, Long infoId, String infoType, String issueStatus, String readStatus,
//      Long personnelId) {
//    StringBuffer hql = new StringBuffer();
//    hql.append("from Information");
//    List<Object> objs = new ArrayList();
//    this.getHql(hql, objs, enterpriseId, personnelId, columnId, isCommon, infoTitle, infoId,
//        infoType, issueStatus, readStatus);
//    return this.findPageList(hql.toString(), objs.toArray());
//  }
//
//  public int queryCount(boolean isCommon, Long enterpriseId, Long columnId, String infoTitle,
//      Long infoId, String infoType, String issueStatus, String readStatus, Long personnelId) {
//    StringBuffer hql = new StringBuffer();
//    hql.append("select count(*) from Information");
//    List<Object> objs = new ArrayList();
//    this.getHql(hql, objs, enterpriseId, personnelId, columnId, isCommon, infoTitle, infoId,
//        infoType, issueStatus, readStatus);
//    Object obj = this.createQuery(hql.toString(), objs.toArray()).uniqueResult();
//    return obj != null ? Integer.parseInt(obj.toString()) : 0;
//  }

//  /**
//   * 查询公司新闻信息.
//   * 
//   * @param isCommon
//   * @param enterpriseId
//   * @param columnId
//   * @param infoTitle
//   * @param infoId
//   * @param infoType
//   * @param issueStatus
//   * @param readStatus
//   * @param personnelId
//   * @return
//   */
//  public List<Information> queryInfo(boolean isCommon, Long enterpriseId, Long columnId,
//      String infoTitle, Long infoId, String infoType, String issueStatus, String readStatus,
//      Long personnelId) {
//    StringBuffer hql = new StringBuffer();
//    hql.append("from Information");
//    List<Object> objs = new ArrayList();
//    this.getHql(hql, objs, enterpriseId, personnelId, columnId, isCommon, infoTitle, infoId,
//        infoType, issueStatus, readStatus);
//    return this.createQuery(hql.toString(), objs.toArray()).list();
//  }

  // /**
  // * 查询信息发布数据量
  // *
  // * @author wpc
  // * @date 2013-8-15 下午4:21:22
  // * @return
  // */
  // public int queryInfoNum(String id, CommonLogView commonLogView) {
  // String dialect =
  // PropertiesFileUtil.getByFileNameAndPropName("application", "hibernate.dialect");
  // StringBuilder sql = new StringBuilder();
  // Object[] o = new Object[] {id};
  // sql.append(" select count(*) tongji from information tt");
  // sql.append(
  // " where tt.enterprise_id in (select e.enterprise_id from enterprise e where e.enterprise_id
  // >?and e.enterprise_number not like '%test%')");
  // String startTime = commonLogView.getStartDate();
  // String endTime = commonLogView.getEndDate();
  // if (StringUtils.isNotBlank(commonLogView.getCityStr())) {
  // String[] cityStrings = commonLogView.getCityStr().split(",");
  // String cityString = "";
  // for (String city : cityStrings) {
  // if (cityString.equals("")) {
  // cityString = "'" + city.trim() + "'";
  // } else {
  // cityString += ",'" + city.trim() + "'";
  // }
  // }
  // sql.append(
  // " and tt.enterprise_id in ( select e.enterprise_id from enterprise e where e.BIZ_CITY in("
  // + cityString + ") ) ");
  // }
  // if (StringUtils.isNotBlank(startTime) && StringUtils.isBlank(endTime)) {
  // if (OAConstant.DIALECT_ORACLE.equals(dialect)) {// fengjinshou 20160301
  // sql.append(" and to_char(tt.create_time,'yyyy-MM-dd hh24:mm:ss') >= ?");
  // } else {
  // sql.append(" and date_format(tt.create_time,'%Y-%m-%d %H:%i:%s') >= ?");
  // }
  // o = new Object[] {id, startTime};
  // } else if (StringUtils.isNotBlank(endTime) && StringUtils.isBlank(startTime)) {
  // if (OAConstant.DIALECT_ORACLE.equals(dialect)) {
  // sql.append(" and to_char(tt.create_time,'yyyy-MM-dd hh24:mm:ss') <= ?");
  // } else {
  // sql.append(" and date_format(tt.create_time,'%Y-%m-%d %H:%i:%s') <= ");
  // }
  // o = new Object[] {id, endTime};
  // } else if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
  // if (OAConstant.DIALECT_ORACLE.equals(dialect)) {
  // sql.append(" and to_char(tt.create_time,'yyyy-MM-dd hh24:mm:ss') >= ?");
  // sql.append(" and to_char(tt.create_time,'yyyy-MM-dd hh24:mm:ss') <= ?");
  // } else {
  // sql.append(" and date_format(tt.create_time,'%Y-%m-%d %H:%i:%s') >= ?");
  // sql.append(" and date_format(tt.create_time,'%Y-%m-%d %H:%i:%s') <= ?");
  // }
  // o = new Object[] {id, startTime, endTime};
  // }
  // Object obj = createSQLQuery(sql.toString(), o).uniqueResult();
  // if (obj != null) {
  // return Integer.parseInt(obj.toString());
  // } else {
  // return 0;
  // }
  // }

  /**
   * 按企业租户Id、创建时间查询所有最新的栏目分类
   * 
   * @param enterpriseId 所属企业租户Id,不能为空
   * @param startDate 创建时间开始范围,不能为空
   * @param issueStatus 发布状态,不能为空
   * @return 所有最新的栏目分类
   */
  public List<Long> findAllNewest(Long enterpriseId, Date startDate, Long issueStatus) {
    List<Long> result = new ArrayList<Long>();
    String sql =
        "select distinct c.column_id from Information c where ((c.enterprise_id = ? and c.issue_status= ?) or (c.enterprise_id is null and c.issue_status = ?)) and c.create_time >?";
    List<Object[]> list = this
        .createQuery(sql, new Object[] {enterpriseId, issueStatus, issueStatus, startDate}).list();
    if (list != null && list.size() != 0) {
      for (int i = 0; i < list.size(); i++) {
        Object o = list.get(i);
        result.add(NumberUtils.toLong(o.toString()));
      }
    }
    return result;
  }

  /**
   * 根据栏目分类、发布状态分页查询信息列表.
   * 
   * @param columnId 所属栏目分类Id
   * @param issueStatus 发布状态
   * @return 信息列表
   */
  public List<Information> findByColumnIdAndIssueStatus(Long columnId, Long issueStatus) {
    String hql = "from Information where columnId=? and issueStatus=?";
    return this.findPageList(hql, new Object[] {columnId, issueStatus});
  }

  /**
   * 根据企业ID、时间区间查询信息数量.
   * 
   * @param entId 所属企业租户Id,不能为空
   * @param startDate 开始查询时间,不能为空
   * @param endDate 结束查询时间,不能为空
   * @return 信息数量
   */
  public int findCountByEnterpriseIdAndIntervalTime(Long entId, Date startDate, Date endDate) {
    String hql =
        "select count(*) from Information where enterpriseId =? and createTime between ? and ? ";
    Object obj = createQuery(hql, new Object[] {entId, startDate, endDate}).uniqueResult();
    return obj != null ? NumberUtils.toInt(obj.toString()) : 0;
  }

  /**
   * 设置信息投票状态.
   * 
   * @param infoId 信息Id,不能为空
   * @param voteStatus 0已结束,1正在进行中
   */
  public void setInformationVoteStatus(Long infoId, String voteStatus) {
    String hql = "update Information set voteStatus=? where infoId=? ";
    createQuery(hql, new Object[] {voteStatus, infoId}).executeUpdate();
  }

  /**
   * 设置信息发布状态.
   * 
   * @param infoId 信息Id,不能为空
   * @param issueStatus 0取消发布,1发布
   */
  public void setInformationIssueStatus(Long infoId, Long issueStatus) {
    String hql = "update Information set issueStatus=? where infoId=?";
    createQuery(hql, new Object[] {issueStatus, infoId}).executeUpdate();
  }

  /**
   * 根据栏目id删除公告.
   * 
   * @param columnId 所属栏目Id,不能为空
   */
  public void deleteByColumnId(Long columnId) {
    String hql = "delete from Information where columnId=?";
    createQuery(hql, new Object[] {columnId}).executeUpdate();
  }


  /**
   * 构建公共组合条件查询语句.
   * 
   * @param hql
   * @param objs
   * @param enterpriseId
   * @param personnelId
   * @param platformInfoId
   * @param issueStatus
   * @param infoTitle
   * @param readStatus
   */
  private void generateAllAndCommonHql(StringBuffer hql, List<Object> objs, Long enterpriseId,
      Long personnelId, Long columnId, Long infoId, String infoType, String infoTitle,
      String issueStatus, String readStatus) {
    // select * from information where (
    // (enterprise_id = 181 and issue_status = 0)
    // or
    // (column_id in (select column_id from info_column where is_system =1) and issue_status =1)
    // )
    // and (
    // (per_bound is null and dept_bound is null and role_bound is null)
    // or
    // (per_bound is not null and per_bound like '%3023%' )
    // )
    // and info_title like '关于%'

    // select * from information where (
    // (enterprise_id = 181 and issue_status = 1))
    // and (
    // (per_bound is null and dept_bound is null and role_bound is null)
    // or
    // (per_bound is not null and per_bound like '%3023%' )
    // )
    // and info_title like '关于%'

    hql.append(" where ");
    // 设定分类查询范围为租户所有分类+系统默认分类
    if (enterpriseId != null && enterpriseId != 0L) {
      hql.append(" ( ");
      hql.append(" (enterpriseId = ? ");
      objs.add(enterpriseId);
      if (StringUtils.isNotBlank(issueStatus)) {
        hql.append(" and issueStatus = ?  ");
        objs.add(issueStatus);
      }
      hql.append(" ) ");
      hql.append(" ) ");
    }
    // 设定信息id
    if (infoId != null && infoId != 0) {
      hql.append(" and infoId = ? ");
      objs.add(infoId);
    }

    // 设定信息类型
    if (StringUtils.isNotBlank(infoType)) {
      hql.append(" and infoType = ? ");
      objs.add(infoType);
    }

    // 设定分类查询条件
    if (columnId != null && columnId != 0) {
      hql.append(" and columnId = ? ");
      objs.add(columnId);
    }

    // 设定人员查询范围
    hql.append(" and ");
    this.personnelCompetenceHql(hql, objs);

    // 设定名称查询条件
    if (StringUtils.isNotBlank(infoTitle)) {
      hql.append(" and ");
      hql.append(" infoTitle like ? ");
      objs.add("%" + infoTitle + "%");
    }

    // 设定未读查询条件
    if (StringUtils.equals(readStatus, "no")) {
      hql.append(" and ");
      hql.append(
          " infoId not in(select infoId from InformationExt where infoId=infoId and personnelId =?) ");
      objs.add(personnelId);
    }

    hql.append(" order by createTime desc ");
  }

//  /**
//   * 查询栏目列表sql语句 建立日期 : Apr 23, 2013 <br>
//   * 作者 : 王阿敏<br>
//   * 
//   * @param info
//   *
//   * @return
//   */
//  private void getHql(StringBuffer hql, List<Object> objs, Long enterpriseId, Long personnelId,
//      Long columnId, boolean isCommon, String infoTitle, Long infoId, String infoType,
//      String issueStatus, String readStatus) {
//    hql.append(" where ");
//    if (isCommon) {
//      if (columnId != null && columnId != 0) {
//        hql.append(" columnId = ? ");
//        objs.add(columnId);
//      }
//    } else {
//      if (enterpriseId != null && enterpriseId != 0) {
//        hql.append(" enterpriseId = ? ");
//        objs.add(enterpriseId);
//      }
//      // 设定人员查询范围
//      hql.append(" and ");
//      this.personnelCompetenceHql(hql, objs);
//
//      // 设定未读查询条件
//      if (StringUtils.equals(readStatus, "no")) {
//        hql.append(" and ");
//        hql.append(
//            " infoId not in(select infoId from InformationExt where infoId=infoId and personnelId =?)  ");
//        objs.add(personnelId);
//      }
//    }
//
//    if (StringUtils.isNotBlank(infoTitle)) {
//      hql.append(" and infoTitle like ? ");
//      objs.add("%" + infoTitle + "%");
//    }
//    if (infoId != null && infoId != 0) {
//      hql.append(" and infoId = ? ");
//      objs.add(infoId);
//    }
//    if (StringUtils.isNotBlank(infoTitle)) {
//      hql.append(" and infoTitle like ? ");
//      objs.add("%" + infoTitle + "%");
//    }
//    if (StringUtils.isNotBlank(infoType)) {
//      hql.append(" and infoType = ? ");
//      objs.add(infoType);
//    }
//    if (StringUtils.isNotBlank(issueStatus)) {
//      hql.append(" and issueStatus = ? ");
//      objs.add(issueStatus);
//    }
//
//    hql.append(" order by createTime desc ");
//  }

  // /**
  // * 公共的hql语句
  // *
  // * @author zhangyong
  // * @date 2014-11-22 下午10:56:43
  // * @param hql
  // * @param personnelId
  // * @param deptId
  // * @param postId
  // * @return
  // */
  // private String commonHql(String hql, Long personnelId, Long deptId, Long postId) {
  // if (personnelId != null) {
  // if (deptId != null && postId != null) {
  // hql += " and ( (c.perBound is not null and c.perBound like '%" + personnelId.toString()
  // + "%' ) or ( c.roleBound is not null and c.roleBound like '%" + postId.toString()
  // + "%' ) or ( c.deptBound is not null and c.deptBound like '%" + deptId.toString()
  // + "%' ) or (c.perBound is null and c.deptBound is null and c.roleBound is null) or
  // (c.createUserId is not null and c.createUserId ="
  // + personnelId + "))";
  // } else if (deptId == null && postId != null) {
  // hql += " and ( (c.perBound is not null and c.perBound like '%" + personnelId.toString()
  // + "%' ) or ( c.roleBound is not null and c.roleBound like '%" + postId.toString()
  // + "%' ) or (c.perBound is null and c.deptBound is null and c.roleBound is null) or
  // (c.createUserId is not null and c.createUserId ="
  // + personnelId + ") )";
  // } else if (deptId != null && postId == null) {
  // hql += " and ( (c.perBound is not null and c.perBound like '%" + personnelId.toString()
  // + "%' ) or ( c.deptBound is not null and c.deptBound like '%" + deptId.toString()
  // + "%' ) or (c.perBound is null and c.deptBound is null and c.roleBound is null) or
  // (c.createUserId is not null and c.createUserId ="
  // + personnelId + ") )";
  // } else {
  // hql += " and ( (c.perBound is not null and c.perBound like '%" + personnelId.toString()
  // + "%' ) or (c.perBound is null and c.deptBound is null and c.roleBound is null) or
  // (c.createUserId is not null and c.createUserId ="
  // + personnelId + ") )";
  // }
  // }
  // return hql;
  // }

  private void personnelCompetenceHql(StringBuffer hql, List<Object> objs) {
    HqlUtils.personnelCompetenceHql(hql, objs);
  }

}

